/*
 * Created on 2005-4-20
 * 
 * Copyright bloomsoft.cn All rights reserved
 */
package org.nanhill.support.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.nanhill.commons.io.FileKit;

/**
 * @author alin
 */
public class ExcelCopySample {
	static final Log log = LogFactory.getLog(ExcelCopySample.class);

	static final String outDir = "src/out/support/poi/";

	private static final String inDir = "src/res/support/poi/";

	public void testReadAndRewrite() throws IOException {
		String inFileName = inDir + "BookIn.xls";
		String outFileName = outDir + "BookOut.xls";
		// File inFile = FileKit.getFile(clz, inFileName);
		// File outFile = FileKit.getFileFirmly(clz, outFileName);
		POIFSFileSystem fs = new POIFSFileSystem(
				new FileInputStream(inFileName));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFCell cell;
		HSSFSheet sheet = wb.getSheetAt(0);

		HSSFRow row = sheet.getRow(2);
		cell = row.getCell((short) 2);
		HSSFCellStyle style = wb.createCellStyle();
		style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);
		style.setTopBorderColor(HSSFColor.BLACK.index);
		cell.setCellStyle(style);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue("a test");
		// cell = row.getCell((short) 3);
		// HSSFCellStyle style = cell.getCellStyle();

		wb.write(new FileOutputStream(outFileName));

	}

	public void testCreate() throws IOException {
		String outFileName = outDir + "TestCreate.xls";
		HSSFWorkbook wb = new HSSFWorkbook();

		HSSFSheet sheet1 = wb.createSheet("�ҿ�");

		HSSFSheet sheet2 = wb.createSheet("freeze pane sheet");
		HSSFSheet sheet3 = wb.createSheet("split pane sheet");

		this.writeExcel(wb, sheet1);
		this.writeExcel(wb, sheet2);
		this.writeExcel(wb, sheet3);

		sheet1.groupRow(5, 14);

		HSSFHeader header = sheet1.getHeader();
		header.setCenter("Center Header");
		header.setLeft("Left Header");
		header.setRight(HSSFHeader.font("Stencil-Normal", "Italic")
				+ HSSFHeader.fontSize((short) 16) + "test create");
		sheet2.createFreezePane(4, 2, 6, 5);
		sheet3.createSplitPane(40, 20, 0, 0, HSSFSheet.PANE_LOWER_LEFT);

		FileOutputStream fsout = new FileOutputStream(outFileName);
		wb.write(fsout);
		fsout.close();
	}

	private void writeExcel(HSSFWorkbook wb, HSSFSheet sheet) {
		HSSFFont font = wb.createFont();
		font.setColor((short) HSSFFont.COLOR_RED);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setStrikeout(true);
		HSSFCell cell;
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style.setBorderBottom(HSSFCellStyle.BORDER_DASH_DOT);
		style.setBottomBorderColor(HSSFColor.RED.index);
		style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);
		style.setTopBorderColor(HSSFColor.BLUE.index);
		style.setBorderLeft(HSSFCellStyle.BORDER_DASHED);
		style.setLeftBorderColor(HSSFColor.YELLOW.index);
		style.setBorderRight(HSSFCellStyle.BORDER_DOUBLE);
		style.setRightBorderColor(HSSFColor.PINK.index);
		font.setFontHeightInPoints((short) 20);
		font.setColor((short) HSSFFont.COLOR_RED);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setStrikeout(true);
		style.setFont(font);

		HSSFRow row = sheet.createRow((short) 1);
		row.createCell((short) 1).setCellValue(1.2);
		cell = row.createCell((short) 2);
		cell.setCellStyle(style);
		cell.setCellValue("This is a string");
		row.createCell((short) 3).setCellValue(true);
		row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR);
		cell = row.createCell((short) 5);

		style = wb.createCellStyle();
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cell.setCellStyle(style);
		cell.setCellValue(new Date());

		row = sheet.createRow((short) 3);
		cell = row.createCell((short) 1);
		style = wb.createCellStyle();
		style.setFillForegroundColor(HSSFColor.ORANGE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		cell.setCellValue("This is a merged region.");
		cell.setCellStyle(style);

		sheet.addMergedRegion(new Region(3, (short) 1, 3, (short) 3));
	}

	public void testDemo() throws IOException {
		short rownum;
		File outFile = FileKit.getFileFirmly(this.getClass(), "workbook.xls");
		// create a new file
		FileOutputStream out = new FileOutputStream(outFile);
		// create a new workbook
		HSSFWorkbook wb = new HSSFWorkbook();
		// create a new sheet
		HSSFSheet s = wb.createSheet();
		// declare a row object reference
		HSSFRow r = null;
		// declare a cell object reference
		HSSFCell c = null;
		// create 3 cell styles
		HSSFCellStyle cs = wb.createCellStyle();
		HSSFCellStyle cs2 = wb.createCellStyle();
		HSSFCellStyle cs3 = wb.createCellStyle();
		HSSFDataFormat df = wb.createDataFormat();
		// create 2 fonts objects
		HSSFFont f = wb.createFont();
		HSSFFont f2 = wb.createFont();

		// set font 1 to 12 point type
		f.setFontHeightInPoints((short) 12);
		// make it blue
		f.setColor((short) 0xc);
		// make it bold
		// arial is the default font
		f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

		// set font 2 to 10 point type
		f2.setFontHeightInPoints((short) 10);
		// make it red
		f2.setColor((short) HSSFFont.COLOR_RED);
		// make it bold
		f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

		f2.setStrikeout(true);

		// set cell stlye
		cs.setFont(f);
		// set the cell format
		cs.setDataFormat(df.getFormat("#,##0.0"));

		// set a thin border
		cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		// fill w fg fill color
		cs2.setFillPattern((short) HSSFCellStyle.SOLID_FOREGROUND);
		// set the cell format to text see HSSFDataFormat for a full list
		cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));

		// set the font
		cs2.setFont(f2);

		// set the sheet name in Unicode
		wb.setSheetName(0, "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F "
				+ "\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430",
				HSSFWorkbook.ENCODING_UTF_16);
		// in case of compressed Unicode
		// wb.setSheetName(0, "HSSF Test",
		// HSSFWorkbook.ENCODING_COMPRESSED_UNICODE );
		// create a sheet with 30 rows (0-29)
		for (rownum = (short) 0; rownum < 30; rownum++) {
			// create a row
			r = s.createRow(rownum);
			// on every other row
			if ((rownum % 2) == 0) {
				// make the row height bigger (in twips - 1/20 of a point)
				r.setHeight((short) 0x249);
			}

			// r.setRowNum(( short ) rownum);
			// create 10 cells (0-9) (the += 2 becomes apparent later
			for (short cellnum = (short) 0; cellnum < 10; cellnum += 2) {
				// create a numeric cell
				c = r.createCell(cellnum);
				// do some goofy math to demonstrate decimals
				c
						.setCellValue(rownum
								* 10000
								+ cellnum
								+ (((double) rownum / 1000) + ((double) cellnum / 10000)));

				// create a string cell (see why += 2 in the
				c = r.createCell((short) (cellnum + 1));

				// on every other row
				if ((rownum % 2) == 0) {
					// set this cell to the first cell style we defined
					c.setCellStyle(cs);
					// set the cell's string value to "Test"
					c.setEncoding(HSSFCell.ENCODING_COMPRESSED_UNICODE);
					c.setCellValue("Test");
				} else {
					c.setCellStyle(cs2);
					// set the cell's string value to "\u0422\u0435\u0441\u0442"
					c.setEncoding(HSSFCell.ENCODING_UTF_16);
					c.setCellValue("\u0422\u0435\u0441\u0442");
				}

				// make this column a bit wider
				s.setColumnWidth((short) (cellnum + 1),
						(short) ((50 * 8) / ((double) 1 / 20)));
			}
		}

		// draw a thick black border on the row at the bottom using BLANKS
		// advance 2 rows
		rownum++;
		rownum++;

		r = s.createRow(rownum);

		// define the third style to be the default
		// except with a thick black border at the bottom
		cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK);

		// create 50 cells
		for (short cellnum = (short) 0; cellnum < 50; cellnum++) {
			// create a blank type cell (no value)
			c = r.createCell(cellnum);
			// set it to the thick black border style
			c.setCellStyle(cs3);
		}

		// end draw thick black border

		// demonstrate adding/naming and deleting a sheet
		// create a sheet, set its title then delete it
		s = wb.createSheet();
		wb.setSheetName(1, "DeletedSheet");
		wb.removeSheetAt(1);
		// end deleted sheet

		// write the workbook to the output stream
		// close our file (don't blow out our file handles
		wb.write(out);
		out.close();
	}
}
